﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using SurveySystem.Model;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MySQLSurveyQuestionRepository : ISurveyQuestionRepository
    {
        #region " Private Utility Methods "
        private static Question QuestionFromDataReader(MySqlDataReader dr)
        {
            Question q = new Question();

            q.QuestionID = dr.GetInt32(0);
            if (!(dr.GetValue(1) is System.DBNull))
                q.SurveyID = dr.GetInt32(1);
            if (!(dr.GetValue(2) is System.DBNull))
                q.QuestionNumber = dr.GetInt32(2);
            if (!(dr.GetValue(3) is System.DBNull))
                q.QuestionText = dr.GetString(3);
            if (!(dr.GetValue(4) is System.DBNull))
                q.Required = dr.GetBoolean(4);
            if (!(dr.GetValue(5) is System.DBNull))
                q.MultiSelect = dr.GetBoolean(5);

            return q;
        }
        #endregion

        public Question GetQuestionByID(int questionID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            Question q = null;

            sql = "SELECT `question_id`, `survey_id`, `question_number`, `question_text`, `required`, `multi_select` \n" + //
                  "FROM `survey_question` WHERE `question_id`=?id";

            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", questionID);

                conn.Open();
                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    q = QuestionFromDataReader(dr);

                }
                else
                {
                    q = null;
                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return q;
        }
        
        public List<Question> GetQuestionsForSurvey(int surveyID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<Question> list = new List<Question>();

            sql = "SELECT `question_id`, `survey_id`, `question_number`, `question_text`, `required`, `multi_select` \n" + //
                  "FROM `survey_question` WHERE `survey_id`=?id";

            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Question q = QuestionFromDataReader(dr);

                    list.Add(q);

                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddQuestion(Question q)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            sql = "INSERT INTO  `survey_question` \n" + //
                  "(`survey_id`,`question_number`,`question_text`,`required`,`multi_select`) \n" + //
                  "VALUES \n" + //
                  "( ?survey_id, ?question_number, ?question_text, ?required, ?multi_select)";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("?survey_id", q.SurveyID);
                cmd.Parameters.AddWithValue("?question_number", q.QuestionNumber);
                cmd.Parameters.AddWithValue("?question_text",  q.QuestionText);
                cmd.Parameters.AddWithValue("?required", q.Required);
                cmd.Parameters.AddWithValue("?multi_select", q.MultiSelect);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();


                //retrieve the new ID field from the database
                sql = "SELECT @@IDENTITY";
                cmd = new MySqlCommand(sql, conn);
                dr = cmd.ExecuteReader();

                // update the Response object
                if (dr.Read())
                {
                    int newid = dr.GetInt32(0);

                    q.QuestionID = newid;

                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void UpdateQuestion(Question q)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "UPDATE `survey_question` SET \n" + //
                  "        `survey_id` = ?survey_id, \n" + //
                  "  `question_number` = ?question_number, \n" + //
                  "    `question_text` = ?question_text, \n" + //
                  "         `required` = ?required, \n" + //
                  "     `multi_select` = ?multi_select \n" + //
                  "WHERE `question_id` = ?question_id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("?question_id", q.QuestionID);
                cmd.Parameters.AddWithValue("?survey_id", q.SurveyID);
                cmd.Parameters.AddWithValue("?question_number", q.QuestionNumber);
                cmd.Parameters.AddWithValue("?question_text", q.QuestionText);
                cmd.Parameters.AddWithValue("?required", q.Required);
                cmd.Parameters.AddWithValue("?multi_select", q.MultiSelect);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void DeleteQuestion(int questionID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "DELETE FROM `survey_question` \n" + //
                  " WHERE `question_id` = ?question_id";

            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?question_id", questionID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }
    }
}
